# -*- coding: utf-8 -*-
#
# The Philatelist program is a fork of the OpenNumismat program
# Copyright (c) 2011 - 2024 Vitaly Ignatov (OpenNumismat) <opennumismat@gmail.com>
# Copyright (c) 2021 - 2024 Victor Nabatov (Philatelist) <nabatov.victor@gmail.com>
#

from PySide6.QtCore import Qt, QSortFilterProxyModel, QObject, QFile, QFileInfo, QDateTime
from PySide6.QtCore import Signal as pyqtSignal
from PySide6.QtGui import QPixmap, QIcon
from PySide6.QtSql import QSqlDatabase, QSqlQuery, QSqlTableModel, QSqlRelationalTableModel, QSqlRelation
from PySide6.QtWidgets import QDialog, QMessageBox, QPushButton

from Philatelist.Reference.ReferenceDialog import ReferenceDialog, CrossReferenceDialog


class SqlTableModel(QSqlTableModel):

    def __init__(self, parent, db):
        super().__init__(parent, db)
        self._proxyModel = QSortFilterProxyModel(self)
        self._proxyModel.setSortLocaleAware(True)
        self._proxyModel.setSourceModel(self)

    def data(self, index, role=Qt.DisplayRole):
        if role == Qt.DecorationRole:
            if index.row() < 0:
                return None

            iconIndex = self.index(index.row(), self.fieldIndex('icon'))
            if not self.data(iconIndex) or self.data(iconIndex).isNull():
                return None

            icon = QPixmap()
            icon.loadFromData(self.data(iconIndex))
            return icon

        return super().data(index, role)

    def proxyModel(self):
        return self._proxyModel

    def sort(self, sort=True):
        if sort:
            self._proxyModel.sort(self.fieldIndex('value'))
        else:
            self._proxyModel.sort(-1)


class SqlRelationalTableModel(QSqlRelationalTableModel):

    def __init__(self, model, parent, db):
        super().__init__(parent, db)
        self.model = model
        self._proxyModel = QSortFilterProxyModel(self)
        self._proxyModel.setSortLocaleAware(True)
        self._proxyModel.setSourceModel(self)

    def relationModel(self, _column):
        return self.model

    def data(self, index, role=Qt.DisplayRole):
        if role == Qt.DecorationRole:
            if index.row() < 0:
                return None

            iconIndex = self.index(index.row(), self.fieldIndex('icon'))
            if not self.data(iconIndex) or self.data(iconIndex).isNull():
                return None

            icon = QPixmap()
            icon.loadFromData(self.data(iconIndex))
            return icon

        return super().data(index, role)

    def proxyModel(self):
        return self._proxyModel

    def sort(self, sort=True):
        if sort:
            self._proxyModel.sort(self.fieldIndex('value'))
        else:
            self._proxyModel.sort(-1)


class BaseReferenceSection(QObject):

    changed = pyqtSignal(object)

    def __init__(self, name, title, letter, sort=False, parent=None):
        super().__init__(parent)
        self.name = name
        self.table_name = "ref_%s" % name
        self.title = title
        self.letter = letter
        self.sort = sort
        self.parent_name = None

    def reload(self):
        self.getSort()
        self.setSort()
        self.model.select()

    def button(self, parent=None):
        self.parent = parent
        button = QPushButton(self.letter, parent)
        button.setFixedWidth(25)
        button.clicked.connect(self.clickedButton)
        return button

    def clickedButton(self):
        old_text = self.parent.text()
        dialog = self._getDialog()
        result = dialog.exec_()
        if result == QDialog.Accepted:
            self.reload()
            index = dialog.selectedIndex()
            if index:
                self.changed.emit(index.data())
            else:
                self.changed.emit(old_text)

        dialog.deleteLater()

    def setSort(self):
        self.model.sort(self.sort)

    def getSort(self):
        query = QSqlQuery(self.db)
        query.prepare("SELECT sort FROM sections WHERE name=?")
        query.addBindValue(self.name)
        query.exec_()
        if query.first():
            data = query.record().value(0)
            if data:
                self.sort = bool(data)
            else:
                self.sort = False

        query.clear()
        return self.sort

    def saveSort(self, sort):
        if self.sort != sort:
            self.sort = sort
            query = QSqlQuery(self.db)
            query.prepare("UPDATE sections SET sort=? WHERE name=?")
            query.addBindValue(int(sort))
            query.addBindValue(self.name)
            query.exec_()

        self.setSort()

    def create(self, db=QSqlDatabase()):
        in_transaction = db.transaction()
        cross_ref = (
            'country', 'series', 'theme', 'cancellation', 'perforation', 'printing',
            'type', 'format', 'material', 'material_type', 'thickness', 'currency'
        )
        variety_ref = ('grid', 'honeycomb', 'raster', 'watermark')
        icon_ref = ('stamp_color', 'glue_color', 'grade', 'place')
        if self.name in cross_ref:
            if self.name not in ('country'):
                sql = "CREATE TABLE %s (\
                id INTEGER PRIMARY KEY,\
                parentid INTEGER,\
                value CHAR)" % self.table_name
            else:
                sql = "CREATE TABLE %s (\
                id INTEGER PRIMARY KEY,\
                parentid INTEGER,\
                value CHAR,\
                icon BLOB)" % self.table_name

        elif self.name in variety_ref:
            sql = "CREATE TABLE %s (id INTEGER PRIMARY KEY, value CHAR, path TEXT)" % self.table_name

        elif self.name in icon_ref:
            sql = "CREATE TABLE %s (id INTEGER PRIMARY KEY, value CHAR, icon BLOB)" % self.table_name
        else:
            sql = "CREATE TABLE %s (id INTEGER PRIMARY KEY, value CHAR)" % self.table_name

        QSqlQuery(sql, db)

        query = QSqlQuery(db)
        if self.name in cross_ref:
            sql = "INSERT INTO sections (name, letter, parent, sort) VALUES (?, ?, ?, ?)"
        else:
            sql = "INSERT INTO sections (name, letter, sort) VALUES (?, ?, ?)"

        query.prepare(sql)
        query.addBindValue(self.name)
        query.addBindValue(self.letter)
        if self.name in cross_ref:
            if self.name == 'country':
                query.addBindValue('region')

            elif self.name in ('material_type', 'thickness'):
                query.addBindValue('material')
            else:
                query.addBindValue('country')

        query.addBindValue(int(self.sort))
        query.exec_()

        if in_transaction:
            db.commit()


class ReferenceSection(BaseReferenceSection):

    def __init__(self, name, title, letter='…', sort=False, parent=None):
        super().__init__(name, title, letter, sort, parent)

    def load(self, db):
        self.db = db
        if self.table_name not in self.db.tables():
            self.create(self.db)

        self.model = SqlTableModel(None, db)
        self.model.setEditStrategy(QSqlTableModel.OnFieldChange)
        self.model.setTable(self.table_name)
        self.reload()

    def _getDialog(self):
        copy = ReferenceSection(
            self.name,
            self.title,
            self.letter,
            self.sort,
            self.parent
        )
        copy.load(self.db)
        return ReferenceDialog(
            copy,
            self.parent.text(),
            self.parent
        )

    def addItem(self, value, icon=None, link=None):
        record = self.model.record()
        record.setValue('value', value)
        if icon:
            record.setValue('icon', icon)

        if link:
            record.setValue('path', link)

        self.model.insertRecord(-1, record)

    def fillFromQuery(self, query):
        while query.next():
            value = query.record().value(0)
            fillQuery = QSqlQuery(self.db)
            fillQuery.prepare(
                "INSERT INTO %s (value) SELECT ? "
                "WHERE NOT EXISTS "
                "(SELECT 1 FROM %s WHERE value=?)" %
                (self.table_name, self.table_name)
            )
            fillQuery.addBindValue(value)
            fillQuery.addBindValue(value)
            fillQuery.exec_()


class CrossReferenceSection(BaseReferenceSection):

    def __init__(self, name, parentRef, title, letter='…', sort=False, parent=None):
        super().__init__(name, title, letter, sort, parent)
        self.parentIndex = None
        self.parentRef = parentRef
        self.parent_name = parentRef.name
        self.parent_table_name = parentRef.table_name

    def load(self, db):
        self.db = db
        if self.table_name not in self.db.tables():
            self.create(self.db)

        self.model = SqlRelationalTableModel(self.parentRef.model, None, db)
        self.model.setJoinMode(QSqlRelationalTableModel.LeftJoin)
        self.model.setEditStrategy(QSqlTableModel.OnFieldChange)
        self.model.setTable(self.table_name)
        parentidIndex = self.model.fieldIndex('parentid')
        self.model.parentidIndex = parentidIndex
        self.model.setRelation(
            parentidIndex, QSqlRelation(self.parent_table_name, 'id', 'value'))

        self.reload()

    def _getDialog(self):
        ref_section = CrossReferenceSection(
            self.name,
            self.parentRef,
            self.title,
            self.letter,
            self.sort,
            self.parent
        )
        ref_section.load(self.db)
        return CrossReferenceDialog(
            ref_section,
            self.parentIndex,
            self.parent.text(),
            self.parent
        )

    def fillFromQuery(self, parentId, query):
        while query.next():
            value = query.record().value(0)
            fillQuery = QSqlQuery(self.db)
            fillQuery.prepare(
                "INSERT INTO %s (value, parentid) SELECT ?, ? "
                "WHERE NOT EXISTS "
                "(SELECT 1 FROM %s WHERE value=? AND parentid=?)" %
                (self.table_name, self.table_name)
            )
            fillQuery.addBindValue(value)
            fillQuery.addBindValue(parentId)
            fillQuery.addBindValue(value)
            fillQuery.addBindValue(parentId)
            fillQuery.exec_()


class Reference(QObject):

    VERSION = 1

    def __init__(self, fields, parent=None, db=None):
        super().__init__(parent)
        if db:
            self.db = db
        else:
            self.db = QSqlDatabase.addDatabase('QSQLITE', "reference")

        self.fileName = None
        self.userFields = [field.name for field in fields.userFields]
        self.sections = []
        ref_region = self.__createReferenceSection(None, fields.region, '±', True)
        ref_country = self.__createReferenceSection(ref_region, fields.country, '±', True)
        self.__createReferenceSection(ref_country, fields.series, '±')
        self.__createReferenceSection(ref_country, fields.theme, '±', True)
        self.__createReferenceSection(ref_country, fields.cancellation, '±', True)
        self.__createReferenceSection(ref_country, fields.perforation, '±', True)
        self.__createReferenceSection(ref_country, fields.printing, '±', True)
        self.__createReferenceSection(ref_country, fields.type, '±', True)
        self.__createReferenceSection(ref_country, fields.format, '±', True)
        self.__createReferenceSection(ref_country, fields.currency, '±', True)
        ref_material = self.__createReferenceSection(ref_country, fields.material, '±', True)
        self.__createReferenceSection(ref_material, fields.material_type, '±', True)
        self.__createReferenceSection(ref_material, fields.thickness)
        self.__createReferenceSection(None, fields.condition)
        self.__createReferenceSection(None, fields.glue)
        self.__createReferenceSection(None, fields.glue_color, '±', True)
        self.__createReferenceSection(None, fields.grid)
        self.__createReferenceSection(None, fields.honeycomb)
        self.__createReferenceSection(None, fields.raster)
        self.__createReferenceSection(None, fields.variety)
        self.__createReferenceSection(None, fields.watermark)
        self.__createReferenceSection(None, fields.grade)
        self.__createReferenceSection(None, fields.expert)
        self.__createReferenceSection(None, fields.buyer)
        self.__createReferenceSection(None, fields.seller)
        if 'pay_place' in self.userFields or 'sale_place' in self.userFields:
            self.sections.append(ReferenceSection('place', self.tr("Place"), '±', True))

        if 'stamp_color1' in self.userFields or 'stamp_color2' in self.userFields:
            self.sections.append(ReferenceSection('stamp_color', self.tr("Color"), '±', True))

    def __createReferenceSection(self, parentRef, field, letter='±', sort=True):
        if field.name in self.userFields:
            if parentRef:
                ref = CrossReferenceSection(
                    field.name,
                    parentRef,
                    field.title,
                    letter,
                    sort
                )
            else:
                ref = ReferenceSection(
                    field.name,
                    field.title,
                    letter,
                    sort
                )
            self.sections.append(ref)
            return ref

        return None

    def create(self):
        sql = "CREATE TABLE IF NOT EXISTS sections (\
            id INTEGER PRIMARY KEY,\
            name CHAR,\
            icon BLOB,\
            letter CHAR,\
            parent CHAR,\
            sort INTEGER)"
        QSqlQuery(sql, self.db)

        sql = """CREATE TABLE ref (title CHAR NOT NULL UNIQUE, value CHAR)"""
        QSqlQuery(sql, self.db)

        query = QSqlQuery(self.db)
        query.prepare("INSERT INTO ref (title, value) VALUES ('version', ?)")
        query.addBindValue(self.VERSION)
        query.exec_()

        for section in self.sections:
            section.create(self.db)

    def open(self, fileName, interactive=True):
        file = QFileInfo(fileName)
        if file.isFile():
            self.db.setDatabaseName(fileName)
            if not self.db.open():
                print(self.db.lastError().text())
                QMessageBox.critical(
                    self.parent(),
                    self.tr("Open reference"),
                    self.tr("Can't open reference:\n%s") % fileName
                )
                return False

        else:
            if interactive:
                QMessageBox.warning(
                    self.parent(),
                    self.tr("Open reference"),
                    self.tr("Can't open reference:\n%s\nCreated new one.") % fileName
                )

            self.db.setDatabaseName(fileName)
            if not self.db.open():
                print(self.db.lastError().text())
                QMessageBox.critical(
                    self.parent(),
                    self.tr("Create reference"),
                    self.tr("Can't create reference:\n%s") % fileName
                )
                return False

            self.create()

        self.fileName = fileName
        return True

    def load(self):
        query = QSqlQuery("SELECT value FROM ref WHERE title='version'", self.db)
        query.exec_()
        if query.first():
            current_version = int(query.record().value(0))
            if current_version > self.VERSION:
                QMessageBox.critical(
                    self.parent(),
                    self.tr("Open reference"),
                    self.tr("Reference is a newer version.\nPlease update Philatelist.")
                )
                return

        for section in self.sections:
            section.load(self.db)

        self.sections_with_icons = []
        for section in self.sections:
            sql = "SELECT 1 FROM %s WHERE icon IS NOT NULL LIMIT 1" % section.table_name
            query = QSqlQuery(sql, self.db)
            query.exec_()
            if query.first():
                self.sections_with_icons.append(section.table_name)

        self.sections_with_links = []
        for section in self.sections:
            query = QSqlQuery(
                "SELECT 1 FROM %s WHERE path IS NOT NULL LIMIT 1" % section.table_name,
                self.db
            )
            query.exec_()
            if query.first():
                self.sections_with_links.append(section.table_name)

    def section(self, name):
        # NOTE: payplace and sale_place fields has one reference section =>
        # editing one of it should change another
        if name in ('pay_place', 'sale_place'):
            name = 'place'

        elif name in ('stamp_color1', 'stamp_color2'):
            name = 'stamp_color'

        for section in self.sections:
            if section.name == name:
                return section

        return None

    def allSections(self):
        sectionNames = []
        for section in self.sections:
            if section.name == 'place':
                sectionNames.extend(['pay_place', 'sale_place'])

            elif section.name == 'stamp_color':
                sectionNames.extend(['stamp_color1', 'stamp_color2'])
            else:
                sectionNames.append(section.name)

        return sectionNames

    def getIcon(self, section, value):
        if section in ('pay_place', 'sale_place'):
            section = 'place'

        elif section in ('stamp_color1', 'stamp_color2'):
            section = 'stamp_color'

        table_name = "ref_%s" % section
        if table_name in self.sections_with_icons:
            sql = "SELECT icon FROM %s WHERE value=?" % table_name
            query = QSqlQuery(sql, self.db)
            query.addBindValue(value)
            query.exec_()
            if query.first():
                data = query.record().value(0)
                if data:
                    pixmap = QPixmap()
                    if pixmap.loadFromData(data):
                        icon = QIcon(pixmap)
                        return icon
        return None

    def backup(self):
        if self.fileName:
            file = QFileInfo(self.fileName)
            backupDir = file.dir()
            backupFileName = backupDir.filePath(
                "%s_%s.ref" % (
                    file.baseName(),
                    QDateTime.currentDateTime().toString('yyMMddhhmmss')
                )
            )
            srcFile = QFile(self.fileName)
            srcFile.copy(backupFileName)
